package com.cn.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.cn.dao.ProductDao;
import com.cn.domain.Product;
import com.cn.utils.JdbcUtils;

public class ProductDaoImpl implements ProductDao {

	private QueryRunner qr = new QueryRunner();
	
	public void saveProduct(Product p) {
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		String sql = "INSERT INTO product "+
					" (id,         "+
					" NAME,        "+
					" price,       "+
					" num,         "+
					" category,    "+
					" description,  "+
					" url          "+
					" )            "+
					" VALUES     "+
					" (null,?,?,?,?,?,?);";
		try {
			qr.update(conn, sql, p.getName(),p.getPrice(),p.getNum(),
					p.getCategory(),p.getDescription(),p.getUrl());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("添加商品失败！");
		}
	}

	public void deleteProduct(Product p) {
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		String sql = "delete from product where id =?";
		try {
			qr.update(conn, sql, p.getId());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("删除商品失败！");
		}
		
	}

	public List<Product> getListByCondition(Product p) {
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		
		String sql = "select * from product where 1=1 ";
		
		List<Object> param = new ArrayList<Object>();
		if(p.getId()!=null && p.getId()!=0){
			sql +=" and id = ? ";
			param.add(p.getId());
		}
		if(p.getName()!=null && !"".equals(p.getName())){
			sql +=" and name like ? ";
			param.add("%"+p.getName()+"%");
		}
		if(p.getCategory()!=null && !"".equals(p.getCategory())){
			sql +=" and category = ? ";
			param.add(p.getCategory());
		}
		if(p.getMinPrice()!=null){
			sql +=" and price >= ? ";
			param.add(p.getMinPrice());
		}
		if(p.getMaxPrice()!=null && p.getMaxPrice()!=0){
			sql +=" and price <= ?";
			param.add(p.getMaxPrice());
		}
		try {
			List<Product> list = qr.query(conn, sql, new BeanListHandler<Product>(Product.class),param.toArray());
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}
	}

	public Product getProductById(Integer id) {
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		String sql = "select * from product where id = ? ";
		try {
			Product product = qr.query(conn, sql, new BeanHandler<Product>(Product.class),id);
			return product;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("根据id查询商品失败!");
		}
	}

	public void updateProduct(Product p) {
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		String sql =" UPDATE product      "+
					" SET                  "+
					" id = ? ,             "+
					" NAME = ? ,           "+
					" price = ? ,          "+
					" num = ? ,            "+
					" category = ?,        "+
					" description = ?,      "+
					" url = ?              "+
					"  WHERE "+
						" id = ? "; 
		System.out.println(sql);
		System.out.println(p.getUrl());
		try {
			qr.update(conn, sql, p.getId(),p.getName(),p.getPrice(),p.getNum(),
					p.getCategory(),p.getDescription(),p.getUrl(),p.getId());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("更新商品失败！");
		}
		
	}

	public int getTotalCount(String key, String category) {
		
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		
		String sql = "select count(*) from product where 1=1 ";
		
		List<Object> param = new ArrayList<Object>();
		
		if(key!=null && !"".equalsIgnoreCase(key)){
			sql +=" and  name like ? ";
			param.add("%"+key+"%");
		}
		if(category!=null && !"".equalsIgnoreCase(category)){
			sql +=" and category like ? ";
			param.add("%"+category+"%");
		}
		System.out.println("category:"+category);
		try {
			Object totalCount =  qr.query(conn, sql, new ScalarHandler(),param.toArray());
			return Integer.parseInt(totalCount+"");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("查询商品总量失败！");
		}
	}

	public List<Product> getProductList(String key, String category, int start, int count) {
		
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		
		String sql = "select * from product where 1=1 ";
		
		List<Object> param = new ArrayList<Object>();
		
		if(key!=null && !"".equalsIgnoreCase(key)){
			sql +=" and  name like ? ";
			param.add("%"+key+"%");
		}
		if(category!=null && !"".equalsIgnoreCase(category)){
			sql +=" and category = ? ";
			param.add(category);
		}
		
		sql +=" limit ?,?";
		param.add(start);
		param.add(count);
		System.out.println(sql);
		System.out.println("category:"+category);
		try {
			List<Product> list =  qr.query(conn, sql, new BeanListHandler<Product>(Product.class),param.toArray());
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("查询分页商品失败！");
		}
	}

	public List<Product> getProductByTipName(String tipname) {
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		
		String sql = "select * from product where name like ? ";
		
		
		try {
			List<Product> list =  qr.query(conn, sql, new BeanListHandler<Product>(Product.class),tipname+"%");
			System.out.println(list);
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("查询商品失败！");
		}
		
	}

	public void updateProductNum(int id, int num) {
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		String sql =" UPDATE product      "+
					" SET                  "+
					" num = ?            "+
					"  WHERE "+
						" id = ? "; 
		System.out.println(sql);
		try {
			qr.update(conn, sql,num,id);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("更新商品失败！");
		}
		
	}

	public List<Product> getTopSaleList(Date date1, Date date2) {
		
		Connection conn = JdbcUtils.getConnectionWithTransaction();
		
		String sql =" SELECT                                          "+
					"  p.id,                                          "+
					"  p.price,                                       "+
					"  p.name,                                        "+
					"  p.category,                                    "+
					"  p.num,                                         "+
					"  SUM(oi.buynum)    saleSum                      "+
					" FROM product p,                                  "+
					"  t_order o,                                     "+
					"  orderitem oi                                   "+
					" WHERE o.id = oi.oid                              "+
					"    AND oi.pid = p.id                            "+
					"    AND o.isdelete = 0                           ";
		if(date1!=null){
			sql+=" and o.ordertime BETWEEN ? and ? ";
		}
		sql+=" GROUP BY p.id,p.price,p.name,p.category,p.num    "+
			 " ORDER BY saleSum desc                            ";
		System.out.println(date1);
		System.out.println(date2);
		List<Product> list= null;
		try {
			if(date1!=null){
				list =  qr.query(conn, sql, new BeanListHandler<Product>(Product.class),date1,date2);
				
			}else{
				list =  qr.query(conn, sql, new BeanListHandler<Product>(Product.class));
			}
			
			System.out.println(list);
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException("查询销售榜单失败！");
		}
	}

}
